﻿using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.library;
using com.nm.library.Helper;
using george.Data;
using InventoryToPredictAPI.model;
using NHibernateVertica;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Text;

namespace InventoryToPredictAPI
{
    public class NdwKnockMatHandler : BaseHandler
    {
        private VerticaDataHelper VerticaHelper { get; set; } = new VerticaDataHelper();

        #region 获取淘汰品列表
        [MustLogin]
        [Description("淘汰品列表")]
        [ReturnType(typeof(PaperData<NdwKnockMatModel.ListModel>))]
        public ApiResult GetNdwKnockMatList([ParamDescription("货号")] string MaterialID = "", [ParamDescription("页码")] int Size = 100, [ParamDescription("页数")] int Page = 1)
        {
            try
            {
                //MaterialID = StringTableHelper.ReturnSplitList(MaterialID, ',');
                var count = ReturnCount(MaterialID);
                var result = PaperData<NdwKnockMatModel.ListModel>.Create(count, Size, Page);
                IList<NdwKnockMatModel.ListModel> list = GetListPage(MaterialID, Size, Page);
                result.Items = list;
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        private IList<NdwKnockMatModel.ListModel> GetListPage(string MaterialIDs, int Size, int Page)
        {
            string sql = string.Empty;
            sql = $@" select *  From ndw.ndw_knockMat a  where a.ExtrSource = '{ UserInfo.UserExtrSource}' and a.CompanyId = '{UserInfo.UserCompanyId}' 
                        {(string.IsNullOrWhiteSpace(MaterialIDs) ? "" : $" AND a.MaterialID like '%{MaterialIDs}%' ")} 
                            LIMIT {Size} offset {(Page - 1) * Size}";
            //{(string.IsNullOrWhiteSpace(MaterialIDs) ? "" : $" AND a.MaterialID in ({MaterialIDs}) ")} 

            var cmd = VerticaHelper.GetSqlStringCommand(sql);
            var dt = VerticaHelper.ExecuteDataTable(cmd);
            var list = ModelConvertHelper<NdwKnockMatModel.ListModel>.ConvertToModel(dt);
            return list;
        }


        #region 返回行数

        public int ReturnCount(string MaterialIDs = "")
        {
            try
            {
                var countSql = string.Empty;
                countSql = $@" select count(1)  From ndw.ndw_knockMat a  where a.ExtrSource = '{ UserInfo.UserExtrSource}' and a.CompanyId  = '{UserInfo.UserCompanyId}' 
                        {(string.IsNullOrWhiteSpace(MaterialIDs) ? "" : $" AND a.MaterialID  like '%{MaterialIDs}%' ")} ";
                var cmd = VerticaHelper.GetSqlStringCommand(countSql);
                var count = Convert.ToInt32(VerticaHelper.ExecuteScalar(cmd));
                return count;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion 返回行数

        #endregion

        #region 删除

        [JsonType]
        [Description("删除数据")]
        [ReturnType(typeof(IList<NdwKnockMatModel.ListRemoveModel>))]
        public ApiResult Remove(NdwKnockMatModel.ListRemoveModel param)
        {

            if (param == null || param.list.Count < 1)
            {
                return Error($"删除失败!");
            }
            HashSet<string> DeleteSql = new HashSet<string>();
            foreach (var item in param.list)
            {
                string deleteSql = $@"Delete FROM ndw.ndw_knockMat
                                    where MaterialID='{item.MaterialID}' AND
	                                ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}';";
                DeleteSql.Add(deleteSql);
            }
            StringBuilder strSql = new StringBuilder();
            foreach (var item in DeleteSql)
            {
                strSql.Append(item);
            }
            if (strSql.Length > 0)
            {
                List<CommandInfo> cmdList = new List<CommandInfo>();
                CommandInfo cmd = new CommandInfo { CommandText = strSql.ToString() };
                cmdList.Add(cmd);
                VerticaHelper.ExecuteSqlTran(cmdList);
                return Success(0);
            }
            return Error($"删除失败!");
        }

        #endregion 删除

        #region 新增

        [JsonType]
        [Description("新增")]
        //[ReturnType(typeof(int), "0－成功")]
        public ApiResult Insert([ParamDescription("货号list")] NdwKnockMatModel.SaveModel MaterialIDs)
        {
            if (MaterialIDs == null || string.IsNullOrWhiteSpace(MaterialIDs.MaterialID))
                return Error("无数据!");
            //排除空字符，以及排除存在NdwKnockMat表中的数据
            AlreadyExists(MaterialIDs.MaterialID, out int count, out List<NdwKnockMatModel.ListModel> param);

            if (param == null || param.Count <= 0)
                return Error("货号重复或不符合规范！");

            if (string.IsNullOrEmpty(UserInfo.UserCompanyId))
            {
                return Error("该用户不属于任何公司!");
            }
            if (string.IsNullOrEmpty(UserInfo.UserExtrSource))
            {
                return Error("该用户所在公司没有任何数据源!");
            }

            if (param == null || param.Count < 1)
            {
                return Error($"新增失败!");
            }

            var savelist = GetMaterialIDList(param, "nods.nods_erp_SD_Mat_Material2");
            if (savelist.Count <= 0)
                return Error($"货号不存在货品资料中！");

            int res = Save(savelist);//保存
            if (res == 0) return Success("保存成功！");
            return Error($"插入失败,请重试!");

        }

        /// <summary>
        /// 保存
        /// </summary>
        /// <param name="param">保存的数据</param>
        /// <returns></returns>
        private int Save(IList<NdwKnockMatModel.ListModel> param)
        {
            string UserExtrSource = UserInfo.UserExtrSource;
            string UserCompanyId = UserInfo.UserCompanyId;
            HashSet<string> InsertSql = new HashSet<string>();
            foreach (var item in param)
            {
                string inserSql = $@"insert into ndw.ndw_knockMat values('{UserExtrSource}','{UserCompanyId}','{item.MaterialID}');";
                InsertSql.Add(inserSql);
            }
            StringBuilder strSql = new StringBuilder();
            foreach (var item in InsertSql)
            {
                strSql.Append(item);
            }
            if (strSql.Length > 0)
            {
                List<CommandInfo> cmdList = new List<CommandInfo>();
                CommandInfo cmd = new CommandInfo { CommandText = strSql.ToString() };
                cmdList.Add(cmd);
                VerticaHelper.ExecuteSqlTran(cmdList);

                return 0;
            }
            return -1;
        }
        //  VerticaHelper.BulkCopy()
        public int SaveToCopy(IList<NdwKnockMatModel.ListModel> param)
        {
            try
            {
                DataTable dt = DataTableHelper.LinqToDataTable(param);
                VerticaHelper.BulkCopy(dt, "ndw.ndw_knockMat");
                return 0;
            }
            catch (Exception ex)
            {
                return -1;

            }
           
        }





        private void AlreadyExists(string MaterialIDs, out int count, out List<NdwKnockMatModel.ListModel> returnList)
        {

            returnList = new List<NdwKnockMatModel.ListModel>();
            string UserExtrSource = UserInfo.UserExtrSource;
            string UserCompanyId = UserInfo.UserCompanyId;
            //查询数据
            IList<NdwKnockMatModel.ListModel> list = GetListPage("", 9999, 1);
            string[] arr = MaterialIDs.TrimEnd(',').Split(',');
            count = 0;
            bool IsSave = true;
            //NdwKnockMat 无数据，直接保存
            if (list.Count <= 0)
            {
                for (int i = 0; i < arr.Length; i++)
                {
                    if (string.IsNullOrEmpty(arr[i]) || arr[i] == "" || arr[i].ToString().Length > 59) continue;
                    returnList.Add(new NdwKnockMatModel.ListModel() { MaterialID = arr[i].ToString(), ExtrSource = UserExtrSource, CompanyID = UserCompanyId });
                }
            }
            else
            {
                for (int i = 0; i < arr.Length; i++)
                {
                    IsSave = true;
                    if (string.IsNullOrEmpty(arr[i]) || arr[i] == "" || arr[i].ToString().Length > 59) continue;
                    foreach (var item in list)
                    {
                        if (arr[i] == item.MaterialID)
                        {
                            IsSave = false;
                            break;
                        }
                        else count++;
                    }
                    //都执行完，都没重复的数据，才能加入。
                    if (IsSave)
                        returnList.Add(new NdwKnockMatModel.ListModel() { MaterialID = arr[i].ToString(), ExtrSource = UserExtrSource, CompanyID = UserCompanyId });
                }
            }


        }

        #endregion

        #region 导入
        /// <summary>
        /// 导入
        /// </summary>
        /// <param name="list"></param>
        /// <param name="error_dt"></param>
        /// <param name="index"></param>
        public void ImportData(IList<NdwKnockMatModel.ListModel> list, out DataTable error_dt, out int index)
        {
            List<NdwKnockMatModel.ListModel> msgList = new List<NdwKnockMatModel.ListModel>();
            List<NdwKnockMatModel.ListModel> saveList = new List<NdwKnockMatModel.ListModel>();
            // AlreadyExists(MaterialIDs.MaterialID, out int count, out List<NdwKnockMatModel.ListModel> param);
            IList<NdwKnockMatModel.ListModel> lis = GetListPage("", 9999, 1);
            int isBreak = 0;
            //排除重复数据
            foreach (var m in list)
            {
                if (string.IsNullOrEmpty(m.MaterialID) || m.MaterialID == "") continue;
                foreach (var i in lis)
                {
                    isBreak = 0;
                    //存在的数据，提取出来返回到Excel
                    if (i.MaterialID == m.MaterialID)
                    {
                        NdwKnockMatModel.ListModel model = new NdwKnockMatModel.ListModel();
                        msgList.Add(new NdwKnockMatModel.ListModel() { MaterialID = "【" + m.MaterialID + "】已存在" });
                        isBreak = 1;
                        break;
                    }
                }
                if (isBreak == 1) continue;
                //对比完都没有,则保存
                saveList.Add(new NdwKnockMatModel.ListModel() { MaterialID = m.MaterialID });
            }

            //检查货号是否符合数据库内容
            var saveToDbList = GetMaterialIDList(saveList, "nods.nods_erp_SD_Mat_Material2");
            if (saveToDbList.Count <= 0) index = 0;


            //正确条数
            if (Save(saveToDbList) == 0)
                index = saveToDbList.Count;
            else
                index = 0;
            error_dt = DataTableHelper.LinqToDataTable(msgList);

        }

        #endregion

        /// <summary>
        /// 获取MaterialID
        /// </summary>
        /// <returns></returns>
        private IList<NdwKnockMatModel.ListModel> GetMaterialIDList(List<NdwKnockMatModel.ListModel> OldList, string TableName)
        {
            string MaterialID = "'";
            foreach (var i in OldList)
            {
                MaterialID += i.MaterialID + "','";
            }
            MaterialID = MaterialID.TrimEnd('\'');
            MaterialID = MaterialID.TrimEnd(',');

            string sql = string.Empty;
            sql = $@" select DISTINCT MaterialID from {TableName} where ExtrSource='{UserInfo.UserExtrSource}' AND MaterialID in({MaterialID}) ";
            var cmd = VerticaHelper.GetSqlStringCommand(sql);
            var dt = VerticaHelper.ExecuteDataTable(cmd);
            var list = ModelConvertHelper<NdwKnockMatModel.ListModel>.ConvertToModel(dt);
            return list;
        }

        //数据处理，未使用。。。
        private IList<NdwKnockMatModel.ListModel> ReturnNoNullList(List<NdwKnockMatModel.ListModel> OldList)
        {
            List<NdwKnockMatModel.ListModel> repeatLis = new List<NdwKnockMatModel.ListModel>();
            if (OldList == null) return null;
            //ndw_knockMat 表重复数据
            var repeatList = GetMaterialIDList(OldList, "ndw.ndw_knockMat");
            foreach (var i in repeatList)
            {
                foreach (var lis in OldList)
                {
                    if (lis.MaterialID == i.MaterialID)
                    {
                        repeatLis.Add(new NdwKnockMatModel.ListModel() { MaterialID = "【" + i.MaterialID + "】已存在" });
                    }
                }

            }
            return GetMaterialIDList(OldList, "nods.nods_erp_SD_Mat_Material2");
        }




    }
}
